package com.lagou.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 分布式定时任务
 */
public class JdbcUtil {

    private static String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";
    private static String user = "root";
    private static String password = "root";
    private static String driver = "com.mysql.jdbc.Driver";

    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            // 通过驱动管理类获取数据库链接
            connection = DriverManager.getConnection(url,user,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 查询
     * @param selectSql
     * @return
     */
    public static List<Map<String, Object>> executeQuery(String selectSql, Object...obj) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            // 连接数据库
            connection = getConnection();
            // 获取预处理statement
            preparedStatement = connection.prepareStatement(selectSql);
            // 给占位符赋值
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            // 向数据库发出sql执行查询，查询出结果集
            resultSet = preparedStatement.executeQuery();
            //
            int count = preparedStatement.getMetaData().getColumnCount();
            while (resultSet.next()) {
                HashMap<String, Object> map = new HashMap<>();
                for (int i = 0; i < count; i++) {
                    Object ob = resultSet.getObject(i + 1);
                    String key = resultSet.getMetaData().getColumnName(i + 1);
                    map.put(key, ob);
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closResource(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 更新
     * @param updateSql
     * @param obj
     */
    public static void executeUpdate(String updateSql, Object...obj) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 连接数据库
            connection = getConnection();
            // 获取预处理statement
            preparedStatement = connection.prepareStatement(updateSql);
            // 给占位符赋值
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            // 向数据库发出sql执行查询
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closResource(null, preparedStatement, connection);
        }
    }

    /**
     * 关闭资源
     */
    public static void closResource(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        // 释放资源
        if (resultSet != null) {
            try{
                resultSet.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try{
                preparedStatement.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try{
                connection.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 插入待归档数据
     */
    public static void main(String[] args) {
        String education = ""; //分片字段
        for (int i = 1; i < 10000; i++) {
            int a = i % 3;
            if (a == 1) {
                education = "master";
            } else if (a == 2) {
                education = "doctor";
            } else {
                education = "bachelor";
            }
            String insertSql = "insert resume (id, name, sex, phone, address, education, state) values (?,?,?,?,?,?,?)";
            executeUpdate(insertSql, i, "name" + i, "sex" + i, "phone" + i, "address" + i, education, "未归档");
            System.out.println(i);
        }
    }
}
